The Three-Schema Architecture

The three-schema architecture#

The goal of the three-schema architecture is to separate the user applications from the physical database.

In this architecture, schemas can be defined at the following three levels:

1. External schema#

An external schema describes the part of the database that a specific user is interested in. It hides the unrelated details of the database from the user like the exact process of retrieving or storing data from the database. There is a different external view for each user of the database.

An external view is just the content of the database as it is seen by one particular user. For example, a user from the sales department will only see sales-related data.

2. Conceptual schema#

The conceptual schema describes the database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc. Usually, a record-based logical data model is used to describe the conceptual schema when a database system is implemented.

3. Internal schema#

The internal schema describes how the database is stored on physical storage devices such as hard drives. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.

Example of the three-schema architecture#

To make things clearer, consider the example of visiting a website through your personal computer.

The web browser on your computer is the external level as you only receive the final web page displayed on your screen without knowing what goes on in the background. The server hosting the website is the conceptual level as it receives your request, retrieves the data you want from the database, and then sends that data back to your computer. The database(stored on some physical media) represents the physical level. It houses the data you are interested in.

Why use the three-schema architecture#

Here are some of the goals that can be achieved using the three schema architecture:

• Every user should be able to access the same data, but be able to see a customized view of the data.

• The user does not need to deal directly with physical database storage details.

• The database administrator should be able to change the database storage structure without disturbing the user’s view.

• The internal structure of the database should remain unaffected when changes are made to the physical aspects of storage.

We will move onto the concept of data independence in the next lesson.